IF DB_ID('Agent') IS NULL CREATE DATABASE Agent;

SET QUOTED_IDENTIFIER OFF;
GO
USE [Agent];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO
-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Clients]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Clients];
GO
IF OBJECT_ID(N'[dbo].[Orders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Orders];
GO
IF OBJECT_ID(N'[dbo].[OrdersProducts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrdersProducts];
GO
IF OBJECT_ID(N'[dbo].[Products]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products];
GO
IF OBJECT_ID(N'[dbo].[Categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Categories];
GO
IF OBJECT_ID(N'[dbo].[AgentsSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AgentsSet];
GO
IF OBJECT_ID(N'[dbo].[OrdePropertiesr1Set]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrdePropertiesr1Set];
GO
IF OBJECT_ID(N'[dbo].[LogSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogSet];
GO
IF OBJECT_ID(N'[dbo].[MyDataSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MyDataSet];
GO
IF OBJECT_ID(N'[dbo].[PriceListSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PriceListSet];
GO
IF OBJECT_ID(N'[dbo].[StockOrders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StockOrders];
GO
IF OBJECT_ID(N'[dbo].[StockOrdersProducts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StockOrdersProducts];
GO
IF OBJECT_ID(N'[dbo].[Notifications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Notifications];
GO
IF OBJECT_ID(N'[dbo].[Regions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Regions];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Regions'
CREATE TABLE [dbo].[Regions](
	[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](50) NOT NULL
);

GO

-- Creating table 'Clients'
CREATE TABLE [dbo].[Clients] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Adress] nvarchar(max)  NOT NULL,
    [AvgOrder] float  NOT NULL,
    [Phone] nvarchar(max)  NULL,
    [EMail] nvarchar(max)  NULL,
    [LastOrder] datetime  NULL,
    [Priority] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [SaleType] nvarchar(max)  NOT NULL,
    [CP] nvarchar(max)  NOT NULL,
    [Region] int  NOT NULL FOREIGN KEY REFERENCES Regions(Id),
    [CreditCardLevel] nvarchar(max)  NOT NULL,
    [ContactPerson] nvarchar(max)  NOT NULL,
    [TotalOrders] float  NOT NULL,
    [x] float  NOT NULL,
    [y] float  NOT NULL
);
GO

-- Creating table 'AgentsSet'
CREATE TABLE [dbo].[AgentsSet] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [UserName] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NULL,
    [Phone] nvarchar(max)  NULL,
    [EMail] nvarchar(max)  NULL,
    [Status] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [TZ] nvarchar(max)  NOT NULL,
    [Region] int  NOT NULL FOREIGN KEY REFERENCES Regions(Id),
    [Whours] float  NULL,
    [Mhours] float  NULL,
    [WavgTime] float  NULL,
    [MavgTime] float  NULL,
    [Worders] int  NOT NULL,
    [Morders] int  NOT NULL,
    [Wincome] float  NOT NULL,
    [Mincome] float  NOT NULL,
    [StartShift] datetime  NULL,
    [EndShift] datetime  NULL
);
GO

-- Creating table 'Orders'
CREATE TABLE [dbo].[Orders] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ClientId] int  NOT NULL FOREIGN KEY REFERENCES Clients(Id),
    [AgentId] int  NOT NULL FOREIGN KEY REFERENCES AgentsSet(Id),
    [Sum] float,
    [Date] DateTime NOT NULL,
    [Remarks] nvarchar(50)
);
GO

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] nvarchar(max)  NOT NULL,
    [Price] float  NOT NULL,
    [CategoryId] INT  NOT NULL FOREIGN KEY REFERENCES Categories(Id),
    [ItemsInStock]  INT NOT NULL DEFAULT 0,
    [Threshold] INT NOT NULL DEFAULT 0, 
    [NotifyBelowThreshold] BIT NOT NULL DEFAULT 0,
    [LastOrderDate] datetime  NULL,
    [MaxDaysFromLastOrder] INT NOT NULL DEFAULT 365,
    [NotifyUnordered] BIT NOT NULL DEFAULT 0,
    [NumOfOrders] int NOT NULL DEFAULT 0,
    [NumOfItemsInAllOrders] int NOT NULL DEFAULT 0
);
GO

-- Creating table 'OrdersProducts'
CREATE TABLE [dbo].[OrdersProducts] (
    [OrderId] int NOT NULL FOREIGN KEY REFERENCES Orders(Id),
    [ProductId] int NOT NULL FOREIGN KEY REFERENCES Products(Id),
    [ItemsNum] int NOT NULL,
    CONSTRAINT pk_OrdersProducts PRIMARY KEY (OrderId,ProductId)
);
GO

-- Creating table 'OrdePropertiesr1Set'
CREATE TABLE [dbo].[OrdePropertiesr1Set] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'LogSet'
CREATE TABLE [dbo].[LogSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Date] datetime  NOT NULL,
    [AgentID] nvarchar(max)  NOT NULL,
    [Path] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'MyDataSet'
CREATE TABLE [dbo].[MyDataSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Distance] float  NULL,
    [LastOrder] float  NULL,
    [AvgMoney] float  NULL,
    [Priority] float  NULL,
    [TotalOrders] float  NULL,
    [Email] nvarchar(max)  NULL
);
GO

-- Creating table 'PriceListSet'
CREATE TABLE [dbo].[PriceListSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [Discount] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'StockOrders'
CREATE TABLE [dbo].[StockOrders] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Date] datetime  NOT NULL,
    [Sum] FLOAT NOT NULL, 
    [IsPending] BIT NOT NULL
);
GO

-- Creating table 'StockOrdersProducts'
CREATE TABLE [dbo].[StockOrdersProducts] (
    [StockOrderId] int NOT NULL FOREIGN KEY REFERENCES StockOrders(Id),
    [ProductId] int NOT NULL FOREIGN KEY REFERENCES Products(Id),
    [ItemsNum] int NOT NULL,
    CONSTRAINT pk_StockOrdersProducts PRIMARY KEY (StockOrderId,ProductId)
);
GO

-- Creating table 'Notifications'
CREATE TABLE [dbo].[Notifications] (
    [Id] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProductId] int NOT NULL FOREIGN KEY REFERENCES Products(Id),
    [NotificationType] int NOT NULL,
    [Date] datetime NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'OrdePropertiesr1Set'
ALTER TABLE [dbo].[OrdePropertiesr1Set]
ADD CONSTRAINT [PK_OrdePropertiesr1Set]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LogSet'
ALTER TABLE [dbo].[LogSet]
ADD CONSTRAINT [PK_LogSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'MyDataSet'
ALTER TABLE [dbo].[MyDataSet]
ADD CONSTRAINT [PK_MyDataSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PriceListSet'
ALTER TABLE [dbo].[PriceListSet]
ADD CONSTRAINT [PK_PriceListSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- -------------------------------------------------
-- Views
-- -------------------------------------------------

-- Creating view 'StockLackNotifications'
CREATE VIEW [dbo].[StockLackNotifications] 
AS
SELECT	Notifications.Id AS NotificationId, Products.Id AS ProductId, Products.Name, 
		Products.ItemsInStock, Products.Threshold, Notifications.Date
FROM Notifications LEFT JOIN Products
ON (Notifications.ProductId = Products.Id)
WHERE (NotificationType = 1);

-- Creating view 'DeadStockNotifications'
CREATE VIEW [dbo].[DeadStockNotifications] 
AS
SELECT	Notifications.Id AS NotificationId, Products.Id AS ProductId, Products.Name, 
		Products.LastOrderDate, Notifications.Date
FROM Notifications LEFT JOIN Products
ON (Notifications.ProductId = Products.Id)
WHERE (NotificationType = 2);

-- -------------------------------------------------
-- Stored Procedures
-- -------------------------------------------------

IF OBJECT_ID('CreateDeadStockNotifications') IS NOT NULL
    DROP PROCEDURE CreateDeadStockNotifications;
GO
CREATE PROCEDURE CreateDeadStockNotifications 
AS
BEGIN
  DECLARE @productId INT;
  
  DECLARE cur CURSOR FOR 
		-- SELECT the products that are dead stock
		SELECT Id
		FROM Products
		WHERE ((LastOrderDate IS NULL) OR 
				(Datediff(day, LastOrderDate, SYSDATETIME()) > 
				MaxDaysFromLastOrder)) AND
				(NotifyUnordered = 1);

  OPEN cur;
  
  FETCH NEXT FROM cur INTO @productId

  WHILE @@FETCH_STATUS = 0
  BEGIN
      -- Delete previous notifications
      DELETE FROM Notifications
      WHERE (NotificationType = 2) AND (ProductId = @productId);
      
	  -- Create the notifications
	  INSERT INTO Notifications (ProductId, NotificationType, Date) 
		VALUES (@productId, 2, SYSDATETIME());
	  FETCH NEXT FROM cur INTO @productId;
  END
  
  CLOSE cur;
END

GO

GRANT EXECUTE ON [dbo].[CreateDeadStockNotifications] TO PUBLIC;

GO

-- =============================================
-- Author:	Vered Shwartz
-- Create date: 29.08.2012
-- Description:	Return top five selling products, 
--		based on number of orders or
--		number of items in all orders. 
-- =============================================
CREATE PROCEDURE [dbo].[GetTopFiveProducts]
	-- The creteria to choose top five products
	@creteria int
AS
BEGIN

    -- Select the top five products by number of orders
	IF (@creteria = 0)
		BEGIN
			SELECT TOP 5 *
			FROM Products
			ORDER BY NumOfOrders DESC; 
		END
	-- Select the top five products by number of items
	ELSE IF (@creteria = 1)

		BEGIN
			SELECT TOP 5 *
			FROM Products
			ORDER BY NumOfItemsInAllOrders DESC; 
		END
	-- Select the top five products by most recently ordered
	ELSE

		BEGIN
			SELECT TOP 5 *
			FROM Products
			ORDER BY LastOrderDate DESC; 
		END
END

GO

-- =============================================
-- Author:		Vered Shwartz
-- Create date: 29.08.2012
-- Description:	Return five least selling products, 
--		based on number of orders or
--		number of items in all orders. 
-- =============================================
CREATE PROCEDURE GetFiveLeastSellingProducts
	-- The creteria to choose top five products
	@creteria int
AS
BEGIN

    -- Select the top five products by number of orders
	IF (@creteria = 0)
		BEGIN
			SELECT TOP 5 *
			FROM Products
			ORDER BY NumOfOrders ASC; 
		END
	-- Select the top five products by number of items
	ELSE IF (@creteria = 1)

		BEGIN
			SELECT TOP 5 *
			FROM Products
			ORDER BY NumOfItemsInAllOrders DESC; 
		END
	-- Least ordered products
	ELSE 
		BEGIN
			SELECT TOP 5 *
			FROM Products
			ORDER BY LastOrderDate ASC; 
		END
END
GO

-- =============================================
-- Author:	Vered Shwartz
-- Create date: 03.09.2012
-- Description:	Retreive the profit and expenses
--	in a certain dates range.
-- =============================================
CREATE PROCEDURE GetBalance

	@startDate datetime, 
	@endDate datetime,  
	@balance float OUTPUT,
	@expenses float OUTPUT,
	@profit float OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT @profit = COALESCE(SUM(Orders.Sum), 0)
	FROM Orders
	WHERE Orders.Date BETWEEN @startDate AND @endDate;

	SELECT @expenses = COALESCE(SUM(StockOrders.Sum), 0)
	FROM StockOrders
	WHERE StockOrders.Date BETWEEN @startDate AND @endDate;

	SET @balance = @profit - @expenses;
END
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------